import { createPool, Pool, PoolOptions, RowDataPacket, OkPacket } from 'mysql2/promise';
import ENV from '../env';

class MySQLConnector {
  private db: Pool;

  constructor(options: PoolOptions) {
    this.db = createPool(options);
  }

  async queryOne(sql: string, params: unknown[]) {
    const rows = await this.query(sql, params);
    return rows.length === 0 ? undefined : rows[0];
  }

  async query(sql: string, params: unknown[]) {
    const [rows] = await this.db.execute(sql, params);
    return rows as RowDataPacket[];
  }

  async execute(sql: string, params: unknown[]) {
    const [rows] = await this.db.execute(sql, params);
    return (rows as OkPacket).affectedRows;
  }

  async insertOneWithAutoIncrement(sql: string, params: unknown[]) {
    const [rows] = await this.db.execute(sql, params);
    return (rows as OkPacket).insertId;
  }
}

export const mainDB = new MySQLConnector({
  host: ENV.MYSQL.MAIN.HOST,
  port: ENV.MYSQL.MAIN.PORT,
  user: ENV.MYSQL.MAIN.USERNAME,
  password: ENV.MYSQL.MAIN.PASSWORD,
  database: ENV.MYSQL.MAIN.DATABASE,
  flags: ['COM_STMT_PREPARE']
});
